﻿Imports System.Data.SqlClient
Public Class frmSoDuHH

    Private Sub frmSoDuHH_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        If aryConfig.Item("Thang") <> "" Then
            cbxNam.Items.Add(aryConfig.Item("Nam"))
            cbxNam.SelectedIndex = 0
            cbxThang.Text = aryConfig.Item("Thang")
            cbxThang.Enabled = False
            cbxNam.Enabled = False
            btnChon.Visible = False

            btnDauTien.Enabled = True
            btnSau.Enabled = True
            btnTruoc.Enabled = True
            btnCuoiCung.Enabled = True
            btnSua.Enabled = True
            btnKhongLuu.Enabled = True
            CapNhatLuoi()

            If dgvList.Rows.Count = 0 Then
                btnSua.Enabled = False
            End If
        Else
            For i As Integer = Now.Year - 5 To Now.Year + 5
                cbxNam.Items.Add(i)
            Next
            cbxNam.Text = Now.Year
            cbxThang.Text = Now.Month
        End If
    End Sub

    Private Sub btnChon_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnChon.Click
        If IsNumeric(cbxThang.Text) Then
            If CapNhatKyHan() Then
                cbxThang.Enabled = False
                cbxNam.Enabled = False
                btnChon.Visible = False

                btnDauTien.Enabled = True
                btnSau.Enabled = True
                btnTruoc.Enabled = True
                btnCuoiCung.Enabled = True
                btnSua.Enabled = True
                btnKhongLuu.Enabled = True
                CapNhatLuoi()
            Else
                MsgBox("Cập nhật thất bại")
            End If
        Else
            MsgBox("Năm không hợp lệ")
        End If
    End Sub

    Private Function CapNhatKyHan() As Boolean
        Dim strSql As String = ""
        Dim myConn As New SqlConnection(modKetNoi.strConnectionString)
        Dim myComm As New SqlCommand
        Try
            myConn.Open()
            myComm.Connection = myConn
            strSql = "Update ThietLap set GiaTri = @GiaTri where TuKhoa = @TuKhoa"
            myComm.CommandText = strSql
            updateValue(cbxThang.Text, "Thang", myComm)
            updateValue(cbxNam.Text, "Nam", myComm)
            Dim thang As Integer = CInt(cbxThang.Text)
            Dim nam As Integer = CInt(cbxNam.Text)
            If thang = 1 Then
                thang = 12
                nam = nam - 1
            Else
                thang = thang - 1
            End If
            strSql = "insert into KhoaSo(Thang, Nam) values(@Thang, @Nam)"
            myComm.CommandText = strSql
            myComm.Parameters.Clear()
            myComm.Parameters.AddWithValue("@Thang", thang)
            myComm.Parameters.AddWithValue("@Nam", nam)
            myComm.ExecuteNonQuery()

            strSql = "select MaHH from HangHoa"
            Dim myAdapter As New SqlDataAdapter(strSql, myConn)
            Dim myTable As New DataTable
            myAdapter.Fill(myTable)

            strSql = "Insert into CTHangHoa (MaHH, Thang, Nam, SLTonDK, GTTonDK, GiaVonDK, KhoaSo) values (@MaHH, @Thang, @Nam, 0, 0, 0, 0)"
            myComm.CommandText = strSql
            For Each row As DataRow In myTable.Rows
                myComm.Parameters.Clear()
                myComm.Parameters.AddWithValue("@MaHH", row(0))
                myComm.Parameters.AddWithValue("@Thang", aryConfig.Item("Thang"))
                myComm.Parameters.AddWithValue("@Nam", aryConfig.Item("Nam"))
                myComm.ExecuteNonQuery()
            Next

        Catch ex As Exception
            Return False
        End Try
        myConn.Close()
        myConn.Dispose()
        Return True
    End Function

    Private Sub updateValue(ByVal value As String, ByVal key As String, ByVal mycomm As SqlCommand)
        value = value.Trim()
        If value <> "" AndAlso value <> modKetNoi.aryConfig(key) Then
            mycomm.Parameters.Clear()
            mycomm.Parameters.AddWithValue("@TuKhoa", key)
            mycomm.Parameters.AddWithValue("@GiaTri", value)
            mycomm.ExecuteNonQuery()
            modKetNoi.aryConfig(key) = value
        End If
    End Sub

    Private Sub CapNhatLuoi()
        Dim strSql As String = "select ROW_NUMBER() OVER (ORDER BY hh.MaHH) as STT, hh.MaHH as [Mã hàng hóa], TenHH as [Tên hàng hóa], convert(VARCHAR,cast(ct.SLTonDK as money),1) [SL Tồn đầu kỳ], convert(VARCHAR,cast(ct.GTTonDK as money),1) as [Giá trị tồn đầu kỳ] from HangHoa hh "
        strSql &= " inner join CTHangHoa ct on ct.MaHH = hh.MaHH and ct.Thang = @Thang and ct.Nam = @Nam and ct.KhoaSo <> 1 order by STT"
        Dim myConn As New SqlConnection(modKetNoi.strConnectionString)
        Dim myTable As New DataTable
        Try
            myConn.Open()
            Dim myAdapter As New SqlDataAdapter(strSql, myConn)
            myAdapter.SelectCommand.Parameters.AddWithValue("@Thang", aryConfig.Item("Thang"))
            myAdapter.SelectCommand.Parameters.AddWithValue("@Nam", aryConfig.Item("Nam"))
            myAdapter.Fill(myTable)
            For Each row As DataRow In myTable.Rows
                row(3) = CDbl(row(3)).ToString("#,###")
                row(4) = CDbl(row(4)).ToString("#,###")
            Next
            dgvList.DataSource = myTable
            dgvList.Columns.Item(0).Width = 50
            dgvList.Columns.Item(1).Visible = False
            dgvList.Columns.Item(2).Width = 200
            dgvList.Columns.Item(3).Width = 150
            dgvList.Columns.Item(3).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
            dgvList.Columns.Item(4).Width = 150
            dgvList.Columns.Item(4).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight

        Catch ex As Exception

        End Try
        myConn.Close()
        myConn.Dispose()
    End Sub

    Private Sub dgvList_SelectionChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles dgvList.SelectionChanged
        If dgvList.SelectedRows.Count > 0 Then
            txtMaHH.Text = dgvList.SelectedRows(0).Cells(1).Value.ToString()
            txtTenHH.Text = dgvList.SelectedRows(0).Cells(2).Value.ToString()
            txtSL.Text = dgvList.SelectedRows(0).Cells(3).Value.ToString()
            txtGiaTri.Text = dgvList.SelectedRows(0).Cells(4).Value.ToString()
        End If
    End Sub

    Private Sub btnSua_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSua.Click
        txtSL.ReadOnly = False
        txtGiaTri.ReadOnly = False
        btnLuu.Enabled = True
    End Sub

    Private Sub btnDong_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDong.Click
        Me.Close()
    End Sub

    Private Sub btnDauTien_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDauTien.Click
        diChuyenChon(1)
    End Sub

    Private Sub btnTruoc_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTruoc.Click
        diChuyenChon(2)
    End Sub

    Private Sub btnSau_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSau.Click
        diChuyenChon(3)
    End Sub

    Private Sub btnCuoiCung_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCuoiCung.Click
        diChuyenChon(4)
    End Sub

    Private Sub diChuyenChon(ByVal kind As Integer)
        If dgvList.Rows.Count > 0 Then
            Dim curIndex As Integer = 0
            If dgvList.CurrentRow IsNot Nothing Then
                curIndex = dgvList.CurrentRow.Index
            End If
            Select Case kind
                Case 1
                    curIndex = 0
                Case 2
                    If curIndex > 0 Then
                        curIndex = curIndex - 1
                    End If
                Case 3
                    If curIndex <> dgvList.Rows.Count - 1 Then
                        curIndex = curIndex + 1
                    End If
                Case 4
                    curIndex = dgvList.Rows.Count - 1
            End Select
            dgvList.CurrentCell = dgvList.Item(0, curIndex)
        End If
    End Sub

    Private Sub btnKhongLuu_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnKhongLuu.Click
        HuyBo()
        If dgvList.SelectedRows.Count > 0 Then
            txtMaHH.Text = dgvList.SelectedRows(0).Cells(1).Value.ToString()
            txtTenHH.Text = dgvList.SelectedRows(0).Cells(2).Value.ToString()
            txtSL.Text = dgvList.SelectedRows(0).Cells(3).Value.ToString()
            txtGiaTri.Text = dgvList.SelectedRows(0).Cells(4).Value.ToString()
        End If
    End Sub

    Private Sub HuyBo()
        txtSL.ReadOnly = True
        txtGiaTri.ReadOnly = True
        btnLuu.Enabled = False
    End Sub

    Private Sub btnLuu_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLuu.Click
        If validateInput() Then
            If CapNhat() Then
                MsgBox("Lưu thành công")
                CapNhatLuoi()
                HuyBo()
            Else
                MsgBox("Lưu thất bại")
            End If
        End If
    End Sub

    Private Function CapNhat() As Boolean
        Dim strSql As String = ""
        Dim myConn As New SqlConnection(modKetNoi.strConnectionString)
        Dim myComm As New SqlCommand
        Dim result As Boolean = True
        Try
            myConn.Open()
            myComm.Connection = myConn
            strSql = "Update CTHangHoa set SLTonDK = @SLTonDK, GTTonDK = @GTTonDK, GiaVonDK = @GiaVonDK where MaHH = @MaHH and Nam = @Nam and Thang = @Thang"
            myComm.CommandText = strSql
            myComm.Parameters.AddWithValue("@Thang", aryConfig.Item("Thang"))
            myComm.Parameters.AddWithValue("@Nam", aryConfig.Item("Nam"))
            myComm.Parameters.AddWithValue("@MaHH", txtMaHH.Text)
            myComm.Parameters.AddWithValue("@SLTonDK", CDbl(txtSL.Text))
            myComm.Parameters.AddWithValue("@GTTonDK", CDbl(txtGiaTri.Text))
            myComm.Parameters.AddWithValue("@GiaVonDK", CDbl(txtGiaTri.Text) / CDbl(txtSL.Text))
            myComm.ExecuteNonQuery()
            CapNhatGiaVonHangBan(txtMaHH.Text, aryConfig.Item("Thang"), aryConfig.Item("Nam"))
        Catch ex As Exception
            result = False
        End Try
        myConn.Close()
        myConn.Dispose()
        Return result
    End Function

    Private Function validateInput() As Boolean
        If Not IsNumeric(Me.txtSL.Text.Trim) Then
            MsgBox("Số lượng phải là kiểu số", MsgBoxStyle.Information)
            txtSL.Focus()
            Return False
        End If

        If Not IsNumeric(Me.txtGiaTri.Text.Trim) Then
            MsgBox("Giá trị phải là kiểu số", MsgBoxStyle.Information)
            txtGiaTri.Focus()
            Return False
        End If
        Return True
    End Function
End Class